import enum
import json
import datetime
from collections import defaultdict, OrderedDict

from sqlalchemy import Table, Column, MetaData, event, func, Integer, String, Float, DateTime, Enum, ForeignKey
from dateutil.relativedelta import relativedelta

sys_serial = {
    "structures": 1,
    "environments": 2,
    "equipments": 3,
    "videos": 4,
    "invades": 5,
    "accesses": 6,
    "patrols": 7,
    "telephones": 8,
    "combustibles": 9,
    "fires": 10
}
area_serial = {
    "起点": 100,
    "101分区": 101,
    "102分区": 102,
    "103分区": 103,
    "104分区": 104,
    "105分区": 105,
    "106分区": 106,
    "107分区": 107,
}
module_serial = {
    "综合舱1": 1,
    "综合舱2": 2,
    "电力舱": 3,
    "供电所": 4,
    "设备间": 5
}

type_serial = {
    "管廊沉降检测仪": 100,
    "管廊扭转检测仪": 200,
    "接缝张开检测仪": 300,
    "液位传感器": 100,
    "温度传感器": 200,
    "湿度传感器": 300,
    "氧气传感器": 400,
    "硫化氢探测器": 500,
    "风机": 100,
    "照明灯开关": 200,
    "排水泵": 300,
    "摄像机": 100,
    "入侵报警探测装置": 100,
    "声光报警器": 200,
    "进门读卡器": 100,
    "人员定位分站": 100,
    "无线基站": 200,
    "防爆有线电话": 100,
    "可燃气体检测报警器": 100,
    "手动报警器": 100,
    "智能烟感传感器": 200,
    "火灾环境测温感温光缆": 300
}


def get_serial(sys_name, area_name, module_name, type_name, number):
    sys_id = sys_serial[sys_name]
    area_id = area_serial[area_name]
    module_id = module_serial[module_name] if module_name else None
    type_id = type_serial[type_name]
    serial = sys_id * 1000 + type_id + number
    if module_name is None:
        return 'L{:03d}_{:03d}_{:04d}'.format(sys_id, area_id, serial)
    return 'L{:03d}_{:03d}_{:03d}_{:04d}'.format(sys_id, area_id, module_id, serial)


json_equipment = ''


def get_equipment_json_datas(sys_name):
    global json_equipment
    if not json_equipment:
        with open('equipment.json', encoding='utf-8') as f:
            json_equipment = json.load(f, object_pairs_hook=OrderedDict)
    return json_equipment[sys_name]


json_definition = ''


def get_definition_json_datas(type_name):
    global json_definition
    if not json_definition:
        with open('definition.json', encoding='utf-8') as f:
            json_definition = json.load(f)
    return json_definition[type_name]


def create_time_trigger(connection, table_name):
    connection.execute("CREATE TRIGGER {0}_update_time BEFORE UPDATE ON {0} "
                       "FOR EACH ROW BEGIN SET NEW.time = NOW(); END;".format(table_name))


def create_time_partition(connection, table_name):
    partitions = []
    today = datetime.date.today()
    partition_date = datetime.date(today.year, today.month, 1) + relativedelta(months=+1)
    while (partition_date < datetime.date(2020, 1, 1)):
        partitions.append(
            'PARTITION p%(year)04d%(month)02d%(day)02d VALUES LESS THAN '
            '(to_days("%(year)04d-%(month)02d-%(day)02d")) ENGINE=InnoDB' %
            {"year": partition_date.year, "month": partition_date.month, "day": partition_date.day})

        partition_date = partition_date + relativedelta(months=+1)

        sql = "ALTER TABLE {0} PARTITION BY RANGE(to_days(time)) ({1});".format(
            table_name, ','.join(partitions))
        connection.execute(sql)


def create_insert_history_trigger(connection, table_name, condition_columns, columns):
    sql = "CREATE TRIGGER {0}_insert_history AFTER UPDATE ON {0} " \
          "FOR EACH ROW BEGIN IF ({1}) THEN " \
          "INSERT INTO {0}_history VALUES ({2}); " \
          "END IF; END;".format(table_name,
                                ' or '.join('NEW.{0} <> OLD.{0}'.format(column) for column in condition_columns),
                                ','.join(['NEW.' + column for column in columns]))
    connection.execute(sql)


class StateEnum(enum.Enum):
    正常 = 0
    断线 = 1
    掉电 = 2
    故障 = 3
    标校 = 4


class AlarmEnum(enum.Enum):
    无报警 = 0
    一般报警 = 1
    严重报警 = 2


metadata = MetaData()

# 创建结构监测系统表
structures = Table('structures', metadata,
                   Column('area', String(16), nullable=False),
                   Column('serial', String(64), primary_key=True),
                   Column('name', String(64), index=True, unique=True, nullable=False),
                   Column('type', String(64), nullable=False),
                   Column('unit', String(64), nullable=False),
                   Column('max_alarm', Float),
                   Column('value', Float),
                   Column('state', Enum(StateEnum), server_default=StateEnum(0).name),
                   Column('alarm', Enum(AlarmEnum), server_default=AlarmEnum(0).name),
                   Column('time', DateTime, server_default=func.now()),
                   Column('mock', String(255))
                   )


# 当结构监测系统表创建成功后，在server中添加自动更新时间的触发器, 初始化数据
@event.listens_for(structures, 'after_create')
def after_structures_create(target, connection, **kw):
    create_time_trigger(connection, target.name)
    create_insert_history_trigger(connection, target.name, ['value', 'state', 'alarm'],
                                  ['area', 'serial', 'type', 'value', 'state', 'alarm', 'time'])

    datas = get_equipment_json_datas(target.name)
    values = []
    type_serial = defaultdict(int)
    for area_name, equipment_data in datas.items():
        for type_name, count in equipment_data.items():
            d = get_definition_json_datas(type_name)
            number = 1
            for i in range(count):
                serial = get_serial(target.name, area_name, None, type_name, number)
                type_serial[type_name] += 1
                name = '{0}{1}号'.format(type_name, type_serial[type_name])
                values.append((area_name, serial, name, type_name, d['unit'], d['max_alarm'], d['value'], d['mock']))
                number += 1

    sql = "INSERT INTO {0}(area, serial, name, type, unit, max_alarm, value, Mock) VALUES {1}".format(
        target.name, ','.join([str(t) for t in values]))
    connection.execute(sql)


# 创建结构监测系统历史表
structures_history = Table('structures_history', metadata,
                           Column('area', String(16), nullable=False),
                           Column('serial', String(64), primary_key=True),
                           Column('type', String(64), nullable=False),
                           Column('value', Float, nullable=False),
                           Column('state', Enum(StateEnum), nullable=False),
                           Column('alarm', Enum(AlarmEnum), nullable=False),
                           Column('time', DateTime, primary_key=True)
                           )


# 当结构监测系统历史表创建成功后，创建分区和触发器
@event.listens_for(structures_history, 'after_create')
def after_structures_history_create(target, connection, **kw):
    create_time_partition(connection, target.name)


# 创建环境监测系统表
environments = Table('environments', metadata,
                     Column('area', String(16), nullable=False),
                     Column('module', String(64), nullable=False),
                     Column('serial', String(64), primary_key=True),
                     Column('name', String(64), index=True, unique=True, nullable=False),
                     Column('type', String(64), nullable=False),
                     Column('unit', String(64), nullable=False),
                     Column('max_alarm', Float),
                     Column('min_alarm', Float),
                     Column('max_range', Float),
                     Column('min_range', Float),
                     Column('value', Float),
                     Column('state', Enum(StateEnum), server_default=StateEnum(0).name),
                     Column('alarm', Enum(AlarmEnum), server_default=AlarmEnum(0).name),
                     Column('time', DateTime, server_default=func.now()),
                     Column('mock', String(255)))


# 当环境监测系统表创建成功后，在server中添加自动更新时间的触发器
@event.listens_for(environments, 'after_create')
def after_environments_create(target, connection, **kw):
    create_time_trigger(connection, target.name)
    create_insert_history_trigger(connection, target.name, ['value', 'state', 'alarm'],
                                  ['area', 'module', 'serial', 'type', 'value', 'state', 'alarm', 'time'])

    datas = get_equipment_json_datas(target.name)
    values = []
    type_serial = defaultdict(int)
    for area_name, module_data in datas.items():
        for module_name, equipment_data in module_data.items():
            for type_name, count in equipment_data.items():
                d = get_definition_json_datas(type_name)
                number = 1
                for i in range(count):
                    serial = get_serial(target.name, area_name, module_name, type_name, number)
                    type_serial[type_name] += 1
                    name = '{0}{1}号'.format(type_name, type_serial[type_name])
                    values.append((area_name, module_name, serial, name, type_name,
                                   d['unit'], d['max_alarm'], d['min_alarm'],
                                   d['max_range'], d['min_range'], d['value'], d['mock']))
                    number += 1

    sql = 'INSERT INTO {0}(area, module, serial, name, type, unit,' \
          ' max_alarm, min_alarm, max_range, min_range, value, mock)' \
          ' VALUES {1}'.format(target.name, ','.join([str(t) for t in values]))
    connection.execute(sql)


# 创建环境监测系统历史表
environments_history = Table('environments_history', metadata,
                             Column('area', String(16), nullable=False),
                             Column('module', String(64), nullable=False),
                             Column('serial', String(64), primary_key=True),
                             Column('type', String(64), nullable=False, index=True),
                             Column('value', Float, nullable=False),
                             Column('state', Enum(StateEnum), nullable=False, index=True),
                             Column('alarm', Enum(AlarmEnum), nullable=False, index=True),
                             Column('time', DateTime, primary_key=True))


# 当环境监测系统历史表创建成功后，创建分区
@event.listens_for(environments_history, 'after_create')
def after_environments_history_create(target, connection, **kw):
    create_time_partition(connection, target.name)


# 创建设备监控系统表
equipments = Table('equipments', metadata,
                   Column('area', String(16), nullable=False),
                   Column('module', String(64), nullable=False),
                   Column('serial', String(64), primary_key=True),
                   Column('name', String(64), index=True, unique=True, nullable=False),
                   Column('type', String(64), nullable=False),
                   Column('voltage', Float),
                   Column('electricity', Float),
                   Column('value', String(64)),
                   Column('state', Enum(StateEnum), server_default=StateEnum(0).name),
                   Column('alarm', Enum(AlarmEnum), server_default=AlarmEnum(0).name),
                   Column('time', DateTime, server_default=func.now()),
                   Column('mock', String(255)))


# 当设备监控系统表创建成功后，在server中添加自动更新时间的触发器
@event.listens_for(equipments, 'after_create')
def after_equipments_create(target, connection, **kw):
    create_time_trigger(connection, target.name)
    create_insert_history_trigger(connection, target.name, ['value', 'state', 'alarm'],
                                  ['area', 'module', 'serial', 'type', 'value', 'state', 'alarm', 'time'])

    datas = get_equipment_json_datas(target.name)
    values = []
    type_serial = defaultdict(int)
    for area_name, module_data in datas.items():
        for module_name, equipment_data in module_data.items():
            for type_name, count in equipment_data.items():
                d = get_definition_json_datas(type_name)
                number = 1
                for i in range(count):
                    serial = get_serial(target.name, area_name, module_name, type_name, number)
                    type_serial[type_name] += 1
                    name = '{0}{1}号'.format(type_name, type_serial[type_name])
                    values.append((area_name, module_name, serial, name, type_name,
                                   d['voltage'], d['electricity'], d['value'], d['mock']))
                    number += 1

    sql = "INSERT INTO {0}(area, module, serial, name, type, voltage, electricity, value, mock)" \
          " VALUES {1}".format(target.name, ','.join([str(t) for t in values]))
    connection.execute(sql)


# 创建设备监控系统历史表
equipments_history = Table('equipments_history', metadata,
                           Column('area', String(16), nullable=False),
                           Column('module', String(64), nullable=False),
                           Column('serial', String(64), primary_key=True),
                           Column('type', String(64), nullable=False),
                           Column('value', String(64), nullable=False),
                           Column('state', Enum(StateEnum), nullable=False),
                           Column('alarm', Enum(AlarmEnum), nullable=False),
                           Column('time', DateTime, primary_key=True))


# 当设备监控系统历史表创建成功后，创建分区
@event.listens_for(equipments_history, 'after_create')
def after_equipments_history_create(target, connection, **kw):
    create_time_partition(connection, target.name)


# 创建视频监控系统表
videos = Table('videos', metadata,
               Column('area', String(16), nullable=False),
               Column('module', String(64), nullable=False),
               Column('serial', String(64), primary_key=True),
               Column('name', String(64), index=True, unique=True, nullable=False),
               Column('type', Integer),
               Column('ip', String(15)),
               Column('port', Integer),
               Column('user', String(64)),
               Column('password', String(64)),
               Column('channel', Integer),
               Column('code', String(255)),
               Column('state', Enum(StateEnum), server_default=StateEnum(0).name))


# 当视频监控系统表创建成功后，初始化数据
@event.listens_for(videos, 'after_create')
def after_vedios_create(target, connection, **kw):
    datas = get_equipment_json_datas(target.name)
    values = []
    type_serial = defaultdict(int)
    for area_name, module_data in datas.items():
        for module_name, equipment_data in module_data.items():
            for type_name, count in equipment_data.items():
                d = get_definition_json_datas(type_name)
                number = 1
                for i in range(count):
                    serial = get_serial(target.name, area_name, module_name, type_name, number)
                    type_serial[type_name] += 1
                    name = '{0}{1}号'.format(type_name, type_serial[type_name])
                    values.append((area_name, module_name, serial, name, d['type'], d['ip'], d['port'],
                                   d['user'], d['password'], d['channel'], d['code']))
                    number += 1

    sql = "INSERT INTO {0}(area, module, serial, name, type, ip, port, user, password, channel, code)" \
          " VALUES {1}".format(target.name, ','.join([str(t) for t in values]))
    connection.execute(sql)


# 创建入侵报警系统表
invades = Table('invades', metadata,
                Column('area', String(16), nullable=False),
                Column('module', String(64), nullable=False),
                Column('serial', String(64), primary_key=True),
                Column('name', String(64), index=True, unique=True, nullable=False),
                Column('type', String(64), nullable=False),
                Column('value', String(64)),
                Column('state', Enum(StateEnum), server_default=StateEnum(0).name),
                Column('alarm', Enum(AlarmEnum), server_default=AlarmEnum(0).name),
                Column('time', DateTime, server_default=func.now()),
                Column('mock', String(255)))


# 当入侵报警系统表创建成功后，在server中添加自动更新时间的触发器
@event.listens_for(invades, 'after_create')
def after_invades_create(target, connection, **kw):
    create_time_trigger(connection, target.name)
    create_insert_history_trigger(connection, target.name, ['value', 'state', 'alarm'],
                                  ['area', 'module', 'serial', 'type', 'value', 'state', 'alarm', 'time'])

    datas = get_equipment_json_datas(target.name)
    values = []
    type_serial = defaultdict(int)
    for area_name, module_data in datas.items():
        for module_name, equipment_data in module_data.items():
            for type_name, count in equipment_data.items():
                d = get_definition_json_datas(type_name)
                number = 1
                for i in range(count):
                    serial = get_serial(target.name, area_name, module_name, type_name, number)
                    type_serial[type_name] += 1
                    name = '{0}{1}号'.format(type_name, type_serial[type_name])
                    values.append((area_name, module_name, serial, name, type_name, d['value'], d['mock']))
                    number += 1

    sql = 'INSERT INTO {0}(area, module, serial, name, type, value, mock)' \
          ' VALUES {1}'.format(target.name, ','.join([str(t) for t in values]))
    connection.execute(sql)


# 创建入侵报警系统历史表
invades_history = Table('invades_history', metadata,
                        Column('area', String(16), nullable=False),
                        Column('module', String(64), nullable=False),
                        Column('serial', String(64), primary_key=True),
                        Column('type', String(64), nullable=False),
                        Column('value', String(64), nullable=False),
                        Column('state', Enum(StateEnum), nullable=False),
                        Column('alarm', Enum(AlarmEnum), nullable=False),
                        Column('time', DateTime, primary_key=True))


# 当入侵报警系统历史表创建成功后，创建分区
@event.listens_for(invades_history, 'after_create')
def after_invades_history_create(target, connection, **kw):
    create_time_partition(connection, target.name)


# 创建出入口控制系统表
accesses = Table('accesses', metadata,
                 Column('area', String(16), nullable=False),
                 Column('module', String(64), nullable=False),
                 Column('serial', String(64), primary_key=True),
                 Column('name', String(64), index=True, unique=True, nullable=False),
                 Column('type', String(64), nullable=False),
                 Column('staff_serial', String(64)),
                 Column('staff_name', String(64)),
                 Column('state', Enum(StateEnum), server_default=StateEnum(0).name),
                 Column('alarm', Enum(AlarmEnum), server_default=AlarmEnum(0).name),
                 Column('time', DateTime, server_default=func.now()))


# 当出入口控制系统表创建成功后，在server中添加自动更新时间的触发器
@event.listens_for(accesses, 'after_create')
def after_accesses_create(target, connection, **kw):
    create_time_trigger(connection, target.name)
    create_insert_history_trigger(connection, target.name, ['staff_serial', 'staff_name', 'state', 'alarm'],
                                  ['area', 'module', 'serial', 'type', 'staff_serial',
                                   'staff_name', 'state', 'alarm', 'time'])

    datas = get_equipment_json_datas(target.name)
    values = []
    type_serial = defaultdict(int)
    for area_name, module_data in datas.items():
        for module_name, equipment_data in module_data.items():
            for type_name, count in equipment_data.items():
                number = 1
                for i in range(count):
                    serial = get_serial(target.name, area_name, module_name, type_name, number)
                    type_serial[type_name] += 1
                    name = '{0}{1}号'.format(type_name, type_serial[type_name])
                    values.append((area_name, module_name, serial, name, type_name))
                    number += 1

    sql = "INSERT INTO {0}(area, module, serial, name, type)" \
          " VALUES {1}".format(target.name, ','.join([str(t) for t in values]))
    connection.execute(sql)


# 创建出入口控制系统历史表
accesses_history = Table('accesses_history', metadata,
                         Column('area', String(16), nullable=False),
                         Column('module', String(64), nullable=False),
                         Column('serial', String(64), primary_key=True),
                         Column('type', String(64), nullable=False, index=True),
                         Column('staff_serial', String(64)),
                         Column('staff_name', String(64)),
                         Column('state', Enum(StateEnum), nullable=False),
                         Column('alarm', Enum(AlarmEnum), nullable=False),
                         Column('time', DateTime, primary_key=True))


# 当出入口控制系统表创建成功后，在server中添加自动更新时间的触发器
@event.listens_for(accesses_history, 'after_create')
def after_accesses_history_create(target, connection, **kw):
    create_time_partition(connection, target.name)


# 创建电子巡查管理系统表
patrols = Table('patrols', metadata,
                Column('area', String(16), nullable=False),
                Column('module', String(64), nullable=False),
                Column('serial', String(64), primary_key=True),
                Column('name', String(64), index=True, unique=True, nullable=False),
                Column('type', String(64), nullable=True),
                Column('value', Integer, nullable=False, default=0),
                Column('state', Enum(StateEnum), server_default=StateEnum(0).name),
                Column('alarm', Enum(AlarmEnum), server_default=AlarmEnum(0).name),
                Column('time', DateTime, server_default=func.now()))


# 当电子巡查管理系统表创建成功后，在server中添加自动更新时间的触发器
@event.listens_for(patrols, 'after_create')
def after_patrols_create(target, connection, **kw):
    create_time_trigger(connection, target.name)

    datas = get_equipment_json_datas(target.name)
    values = []
    type_serial = defaultdict(int)
    for area_name, module_data in datas.items():
        for module_name, equipment_data in module_data.items():
            for type_name, count in equipment_data.items():
                number = 1
                for i in range(count):
                    serial = get_serial(target.name, area_name, module_name, type_name, number)
                    type_serial[type_name] += 1
                    name = '{0}{1}号'.format(type_name, type_serial[type_name])
                    values.append((area_name, module_name, serial, name, type_name, 0))
                    number += 1

    sql = "INSERT INTO {0}(area, module, serial, name, type, value)" \
          " VALUES {1}".format(target.name, ','.join([str(t) for t in values]))
    connection.execute(sql)


# 创建可燃气体泄漏报警系统表
combustibles = Table('combustibles', metadata,
                     Column('area', String(16), nullable=False),
                     Column('module', String(64), nullable=False),
                     Column('serial', String(64), primary_key=True),
                     Column('name', String(64), index=True, unique=True, nullable=False),
                     Column('type', String(64), nullable=False),
                     Column('unit', String(64), nullable=False),
                     Column('max_alarm', Float),
                     Column('min_alarm', Float),
                     Column('max_range', Float),
                     Column('min_range', Float),
                     Column('value', Float),
                     Column('state', Enum(StateEnum), server_default=StateEnum(0).name),
                     Column('alarm', Enum(AlarmEnum), server_default=AlarmEnum(0).name),
                     Column('time', DateTime, server_default=func.now()),
                     Column('mock', String(255)))


# 当可燃气体泄漏报警系统表创建成功后，在server中添加自动更新时间的触发器
@event.listens_for(combustibles, 'after_create')
def after_combustibles_create(target, connection, **kw):
    create_time_trigger(connection, target.name)
    create_insert_history_trigger(connection, target.name, ['value', 'state', 'alarm'],
                                  ['area', 'module', 'serial', 'type', 'value', 'state', 'alarm', 'time'])

    datas = get_equipment_json_datas(target.name)
    values = []
    type_serial = defaultdict(int)
    for area_name, module_data in datas.items():
        for module_name, equipment_data in module_data.items():
            for type_name, count in equipment_data.items():
                d = get_definition_json_datas(type_name)
                number = 1
                for i in range(count):
                    serial = get_serial(target.name, area_name, module_name, type_name, number)
                    type_serial[type_name] += 1
                    name = '{0}{1}号'.format(type_name, type_serial[type_name])
                    values.append((area_name, module_name, serial, name, type_name,
                                   d['unit'], d['max_alarm'], d['min_alarm'],
                                   d['max_range'], d['min_range'], d['value'], d['mock']))
                    number += 1

    sql = "INSERT INTO {0}" \
          "(area, module, serial, name, type, unit, max_alarm, min_alarm, max_range, min_range, value, mock)" \
          " VALUES {1}".format(target.name, ','.join([str(t) for t in values]))
    connection.execute(sql)


# 创建可燃气体泄漏报警系统历史表
combustibles_history = Table('combustibles_history', metadata,
                             Column('area', String(16), nullable=False),
                             Column('module', String(64), nullable=False),
                             Column('serial', String(64), primary_key=True),
                             Column('type', String(64), nullable=False),
                             Column('value', Float, nullable=False),
                             Column('state', Enum(StateEnum), nullable=False),
                             Column('alarm', Enum(AlarmEnum), nullable=False),
                             Column('time', DateTime, primary_key=True))


# 当可燃气体泄漏报警系统历史表创建成功后，创建分区
@event.listens_for(combustibles_history, 'after_create')
def after_combustibles_history_create(target, connection, **kw):
    create_time_partition(connection, target.name)


# 创建火灾报警系统表
fires = Table('fires', metadata,
              Column('area', String(16), nullable=False),
              Column('module', String(64), nullable=False),
              Column('serial', String(64), primary_key=True),
              Column('name', String(64), index=True, unique=True, nullable=False),
              Column('type', String(64), nullable=False),
              Column('value', String(64)),
              Column('state', Enum(StateEnum), server_default=StateEnum(0).name),
              Column('alarm', Enum(AlarmEnum), server_default=AlarmEnum(0).name),
              Column('time', DateTime, server_default=func.now()),
              Column('mock', String(255)))


# 当火灾报警系统表创建成功后，在server中添加自动更新时间的触发器
@event.listens_for(fires, 'after_create')
def after_fires_create(target, connection, **kw):
    create_time_trigger(connection, target.name)
    create_insert_history_trigger(connection, target.name, ['value', 'state', 'alarm'],
                                  ['area', 'module', 'serial', 'type', 'value', 'state', 'alarm', 'time'])

    datas = get_equipment_json_datas(target.name)
    values = []
    type_serial = defaultdict(int)
    for area_name, module_data in datas.items():
        for module_name, equipment_data in module_data.items():
            for type_name, count in equipment_data.items():
                d = get_definition_json_datas(type_name)
                number = 1
                for i in range(count):
                    serial = get_serial(target.name, area_name, module_name, type_name, number)
                    type_serial[type_name] += 1
                    name = '{0}{1}号'.format(type_name, type_serial[type_name])
                    values.append((area_name, module_name, serial, name, type_name, d['value'], d['mock']))
                    number += 1

    sql = "INSERT INTO {0}(area, module, serial, name, type, value, mock)" \
          " VALUES {1}".format(target.name, ','.join([str(t) for t in values]))
    connection.execute(sql)


# 创建火灾报警系统历史表
fires_history = Table('fires_history', metadata,
                      Column('area', String(16), nullable=False),
                      Column('module', String(64), nullable=False),
                      Column('serial', String(64), primary_key=True),
                      Column('type', String(64), nullable=False),
                      Column('value', String(64), nullable=False),
                      Column('state', Enum(StateEnum), nullable=False),
                      Column('alarm', Enum(AlarmEnum), nullable=False),
                      Column('time', DateTime, primary_key=True))


# 当火灾报警系统表创建成功后，在server中添加自动更新时间的触发器
@event.listens_for(fires_history, 'after_create')
def after_fires_history_create(target, connection, **kw):
    create_time_partition(connection, target.name)


# 创建员工定位表
employees = Table('employees', metadata,
                  Column('serial', String(64), primary_key=True),
                  Column('dept', String(64)),
                  Column('name', String(16)),
                  Column('patrol_serial', ForeignKey('patrols.serial')),
                  Column('time', DateTime, server_default=func.now()))


# 当员工定位表创建成功后，插入人员信息
@event.listens_for(employees, 'after_create')
def after_emp_create(target, connection, **kw):
    create_time_trigger(connection, target.name)
    create_insert_history_trigger(connection, target.name, ['patrol_serial'], ['serial', 'patrol_serial', 'time'])

    '''
        connection.execute("CREATE TRIGGER {0}_insert_history AFTER UPDATE ON {0} "
                       "FOR EACH ROW BEGIN "
                       "IF (NEW.patrol_id <> OLD.patrol_ID) THEN "
                       "INSERT INTO {0}_history VALUES (NEW.serial, NEW.patrol_id, NEW.time); "
                       "END IF; END;".format(target.name))
    connection.execute("CREATE TRIGGER {0}_update_patrol AFTER UPDATE ON {0} "
                       "FOR EACH ROW BEGIN "
                       "UPDATE patrols SET value=(SELECT COUNT(id) FROM {0} WHERE patrol_id = OLD.patrol_id) "
                       "WHERE id = old.patrol_id;"
                       "UPDATE patrols SET value=(SELECT COUNT(id) FROM {0} WHERE patrol_id = NEW.patrol_id) "
                       "WHERE id = NEW.patrol_id; END;".format(target.name))
    '''
    emps = [
        ('10001', '设备科', '任桂英'),
        ('10002', '技术科', '萧军'),
        ('10003', '材料科', '方军'),
        ('10004', '质安科', '彭勇'),
        ('10005', '经营部', '任秀兰')
    ]

    sql = "INSERT INTO {0}(serial, dept, name)" \
          " VALUES {1}".format(target.name, ','.join([str(_emp) for _emp in emps]))
    connection.execute(sql)


# 创建员工定位历史表
employees_history = Table('employees_history', metadata,
                          Column('serial', String(64), primary_key=True),
                          Column('patrol_serial', String(64), nullable=False),
                          Column('time', DateTime, primary_key=True))


# 当员工定位历史表创建成功后，插入人员信息
@event.listens_for(employees_history, 'after_create')
def after_employees_history_create(target, connection, **kw):
    create_time_partition(connection, target.name)
